using System;
using System.Data;
using System.Data.SqlClient;
namespace DAL
{
    /**
     * Classname : HocVien
     * Version information :1.0
     * Date  :26-09-2006
     * Copyright notice : 
     */

    public class DAHocVienLV3LV5: DbObject
    {
        protected SqlConnection connec;

        public DAHocVienLV3LV5(SqlConnection Connection)
        {
            connec = Connection;
        }
        // return all the HocViens select Language
        // Danh cho nguoi quan tri
        public int loadDemSoHocVienLV3LV5ByTrangThai(int LevelID, int TrangThaiID)
        {
            int numAffected;
            SqlParameter[] parameters = {   new SqlParameter("@LevelID", SqlDbType.Int, 4) ,
                                            new SqlParameter("@TrangThaiID", SqlDbType.Int, 4) 
                                        };
            parameters[0].Value = LevelID;
            parameters[1].Value = TrangThaiID;

            // create the parameter
            DataSet dsHocVien = RunProcedure("sp_HocVienLV3LV5_loadDemSoHocVienLV3LV5ByTrangThai", parameters, "TB_L2", connec);
            if (dsHocVien != null && dsHocVien.Tables[0].Rows.Count > 0)
            {
                numAffected = Convert.ToInt32(dsHocVien.Tables[0].Rows[0]["Tong"].ToString());
            }
            else
            {
                numAffected = 0;
            }
            return numAffected;
        }
        // return all the HocViens select Language
        // Danh cho nguoi quan tri
        public DataSet loadHocVienLV3LV5()
        {
            // create the parameter
            DataSet dsHocVien = RunProcedure("sp_HocVienLV3LV5_loadLV3LV5", "TB_L3_5B", connec);
            for (int i = 0; i < dsHocVien.Tables[0].Rows.Count; i++)
            {
                dsHocVien.Tables[0].Rows[i]["Seq"] = (i + 1).ToString();
            }
            return dsHocVien;
        }

        // return only the record with the specified ID!!!!!!!!!!!
        public DataRow loadHocVienLV3LV5ID(int HocVienLV3LV5Id, int LevelID)
        {
            // create the parameter
            SqlParameter[] parameters = { new SqlParameter("@HocVienID", SqlDbType.Int, 4), 
                                             new SqlParameter("@LevelID", SqlDbType.Int, 4)
                                        };
            parameters[0].Value = HocVienLV3LV5Id;
            parameters[1].Value = LevelID;
            using (DataSet HocViens = RunProcedure("sp_HocVienLV3LV5_loadLV3LV5ID", parameters, "TB_L3_5B", connec))
            {
                return HocViens.Tables[0].Rows[0];
            }
        }

	
        // update the properties of the specified HocViens
        // Sua noi dung ban ghi hien hanh
        public int updateHocVienLV3LV5(int ID,int HocVienID, string LichHen, string LyDoTuChoi, string GoiLai, string GoiLan1,
                    string GoiLan2, string GoiLan3, string Ghichu1, string Ghichu2,string Ghichu3,
                    string GhiChuKhac,string LydoChuaGap,string DangKyDuTuyen,string NenGoiLaiGap,string NopPhieu,
                   string NenGoiLaiDangKy, int TrangThaiID, int LevelID, int NguoiDungID, DateTime NgayGoi)
        {
            int numAffected;
            // create the parameters
            SqlParameter[] parameters = { 	    new SqlParameter("@HocVienID", SqlDbType.Int, 4),
                                                new SqlParameter("@LichHen", SqlDbType.NVarChar, 256),
                                                new SqlParameter("@LyDoTuChoi", SqlDbType.NVarChar, 256),
                                                new SqlParameter("@GoiLai", SqlDbType.NVarChar,256),
                                                new SqlParameter("@GoiLan1", SqlDbType.NVarChar,256),
                                                new SqlParameter("@GoiLan2", SqlDbType.NVarChar,256),
                                                new SqlParameter("@GoiLan3", SqlDbType.NVarChar,256),
                                                new SqlParameter("@Ghichu1", SqlDbType.NVarChar,256),
                                                new SqlParameter("@Ghichu2", SqlDbType.NVarChar,256),
                                                new SqlParameter("@Ghichu3", SqlDbType.NVarChar,256),
                                                new SqlParameter("@GhiChuKhac", SqlDbType.NVarChar ,1024),
                                                new SqlParameter("@LydoChuaGap", SqlDbType.NVarChar, 1024),	
										        new SqlParameter("@DangKyDuTuyen", SqlDbType.NVarChar,256),
                                                new SqlParameter("@NenGoiLaiGap", SqlDbType.NVarChar,256),	
                                                new SqlParameter("@NopPhieu", SqlDbType.NVarChar,256),	
                                                new SqlParameter("@NenGoiLaiDangKy", SqlDbType.NVarChar,256),
                                                new SqlParameter("@TrangThaiID", SqlDbType.Int, 4),
                                                new SqlParameter("@LevelID", SqlDbType.Int, 4),
                                                new SqlParameter("@NguoiDungID", SqlDbType.Int ,4),
                                                new SqlParameter("@NgayGoi", SqlDbType.DateTime),
                                                new SqlParameter("@ID", SqlDbType.Int ,4)
										};
            // set the values
            parameters[0].Value = HocVienID;
            parameters[1].Value = LichHen;
            parameters[2].Value = LyDoTuChoi.Trim();
            parameters[3].Value = GoiLai.Trim();
            parameters[4].Value = GoiLan1;
            parameters[5].Value = GoiLan2.Trim();
            parameters[6].Value = GoiLan3.Trim();
            parameters[7].Value = Ghichu1.Trim();
            parameters[8].Value = Ghichu2;
            parameters[9].Value = Ghichu3;
            parameters[10].Value = GhiChuKhac;
            parameters[11].Value = LydoChuaGap;
            parameters[12].Value = DangKyDuTuyen;
            parameters[13].Value = NenGoiLaiGap;
            parameters[14].Value = NopPhieu;
            parameters[15].Value = NenGoiLaiDangKy;
            parameters[16].Value = TrangThaiID;
            parameters[17].Value = LevelID;
            parameters[18].Value = NguoiDungID;
            parameters[19].Value = NgayGoi;
            parameters[20].Value = ID;
            // run the procedure
            RunProcedure("sp_HocVienLV3LV5_UpdateLV3LV5", parameters, out numAffected, connec);

            return numAffected;
        }

        // add a new question
        // Them ban ghi hien hanh
        public int insertHocVienLV3LV5(int HocVienID, string LichHen, string LyDoTuChoi, string GoiLai, string GoiLan1,
                                string GoiLan2, string GoiLan3, string Ghichu1, string Ghichu2, string Ghichu3,
                                string GhiChuKhac, string LydoChuaGap, string DangKyDuTuyen, string NenGoiLaiGap, string NopPhieu,
                               string NenGoiLaiDangKy, int TrangThaiID, int LevelID, int NguoiDungID, DateTime NgayGoi,
                                string NoiDungNgayGoi, string NoiDungTaiSao, int intTrangThaiGoiID, int DiemL3_5B, string LyDoChuaNhan)
        {
            int numAffected;
            // create the parameters
            SqlParameter[] parameters =  {	    new SqlParameter("@ID", SqlDbType.Int ,4),
                                                new SqlParameter("@HocVienID", SqlDbType.Int, 4),
                                                new SqlParameter("@LichHen", SqlDbType.NVarChar, 256),
                                                new SqlParameter("@LyDoTuChoi", SqlDbType.NVarChar, 256),
                                                new SqlParameter("@GoiLai", SqlDbType.NVarChar,256),
                                                new SqlParameter("@GoiLan1", SqlDbType.NVarChar,256),
                                                new SqlParameter("@GoiLan2", SqlDbType.NVarChar,256),
                                                new SqlParameter("@GoiLan3", SqlDbType.NVarChar,256),
                                                new SqlParameter("@Ghichu1", SqlDbType.NVarChar,256),
                                                new SqlParameter("@Ghichu2", SqlDbType.NVarChar,256),
                                                new SqlParameter("@Ghichu3", SqlDbType.NVarChar,256),
                                                new SqlParameter("@GhiChuKhac", SqlDbType.NVarChar ,1024),
                                                new SqlParameter("@LydoChuaGap", SqlDbType.NVarChar, 1024),	
										        new SqlParameter("@DangKyDuTuyen", SqlDbType.NVarChar,256),
                                                new SqlParameter("@NenGoiLaiGap", SqlDbType.NVarChar,256),	
                                                new SqlParameter("@NopPhieu", SqlDbType.NVarChar,256),	
                                                new SqlParameter("@NenGoiLaiDangKy", SqlDbType.NVarChar,256),
                                                new SqlParameter("@TrangThaiID", SqlDbType.Int, 4),
                                                new SqlParameter("@LevelID", SqlDbType.Int, 4),
                                                new SqlParameter("@NguoiDungID", SqlDbType.Int ,4),
                                                new SqlParameter("@NgayGoi", SqlDbType.DateTime),	
                                                new SqlParameter("@NoiDungNgayGoi", SqlDbType.NVarChar, 2000),	
                                                new SqlParameter("@NoiDungTaiSao", SqlDbType.NVarChar, 2000),
	                                            new SqlParameter("@TrangThaiGoiID", SqlDbType.Int, 4),
	                                            new SqlParameter("@Diem", SqlDbType.Int, 4),
                                                new SqlParameter("@LyDoChuaNhan", SqlDbType.NVarChar, 2000)
										 };
            // set the values
            parameters[0].Direction = ParameterDirection.Output;
            parameters[1].Value = HocVienID;
            parameters[2].Value = LichHen;
            parameters[3].Value = LyDoTuChoi.Trim();
            parameters[4].Value = GoiLai.Trim();
            parameters[5].Value = GoiLan1;
            parameters[6].Value = GoiLan2.Trim();
            parameters[7].Value = GoiLan3.Trim();
            parameters[8].Value = Ghichu1.Trim();
            parameters[9].Value = Ghichu2;
            parameters[10].Value = Ghichu3;
            parameters[11].Value = GhiChuKhac;
            parameters[12].Value = LydoChuaGap;
            parameters[13].Value = DangKyDuTuyen;
            parameters[14].Value = NenGoiLaiGap;
            parameters[15].Value = NopPhieu;
            parameters[16].Value = NenGoiLaiDangKy;
            parameters[17].Value = TrangThaiID;
            parameters[18].Value = LevelID;
            parameters[19].Value = NguoiDungID;
            parameters[20].Value = NgayGoi;
            parameters[21].Value = NoiDungNgayGoi;
            parameters[22].Value = NoiDungTaiSao;
            parameters[23].Value = intTrangThaiGoiID;
            parameters[24].Value = DiemL3_5B;
            parameters[25].Value = LyDoChuaNhan;
            

            // run the procedure
            RunProcedure("sp_HocVienLV3LV5_insertLV3LV5Main", parameters, out numAffected, connec);

            //return (int)parameters[0].Value;
            return numAffected;
        }
        // Xoa Role
        public int removeHocVienLV3LV5(int HocVienID)
        {
            int numAffected;
            // create the parameters
            SqlParameter[] parameters = { new SqlParameter("@ID", SqlDbType.Int) };
            parameters[0].Value = HocVienID;
            // run the procedure
            RunProcedure("sp_HocVienLV3LV5_removeLV3LV5", parameters, out numAffected, connec);
            return numAffected;
        }
    }
}